# RR下解决幻读的关键next-key lock
# 大纲
要想思路完整, 多问自己几个问题
- 什么是next-key lock
- 什么情况下用到了它
- 它帮我们做了什么
- 它会导致什么问题
# 简介
本文尝试介绍next-key lock.
# 什么是next-key lock
首先说说mysql的锁, 有三种锁算法:
- record lock: 单行上锁
- gap lock: 间隙锁
- next-key lock: 1+2, 锁定单行时也锁定一个范围.
next-key lock是为了避免幻读而引入的锁, 在一定程度上解决了幻读.
# 什么情况下用到了它
前面说到, 是为了解决幻读的问题, 那mysql是什么情况使用到了它呢?
答案是, 在
repeatable read隔离级别下引入了next-key lock, 但RC下也是存在间隙锁的, 相对较复杂.
# 它帮我们做了什么
一般只在RR下存在
# 等于加行锁同时添加了间隙锁
因为next-key lock=行锁+间隙锁, 因此锁定行数据时, 会同时加上间隙锁.
# next-key lock的作用
在RR隔离级别下的事务中, 出现
增删改则会使用next-key lock, 此时不仅锁定了当前行, 也锁定了前后的间隙, 使得无法在间隙中进行insert操作.间隙锁是会将当前行到两边的数据行中间的间隙上锁, 并且是一个
左开右闭区间.
# next-key lock加锁原则
1. 以next-key lock为最小单位
2. 索引上的等值查询, 且是唯一索引, 降级为行锁
3. 索引上的等值查询, 向右遍历到最后一个不满足条件时, 右闭改为右开, 降级为间隙锁
4. limit能减小间隙锁范围, 指定n则最多遍历到n个就结束的右闭区间
5. 普通索引上是存在主键索引的, 因此
非覆盖索引同时锁定了普通索引和主键索引的组合6. order by desc会再向
左(实际上倒序后还是向右)多遍历一次, 直到最后两个不满足条件时结束7. 只有访问到的对象才会加锁(覆盖索引不会访问主键,因此不会锁住主键)
8. 有唯一性的索引上的范围查询, 会向右遍历到最后一个不满足条件的索引, 并且
不降级RC下的优化原则
语句执行过程中会加行锁, 执行完后, 会把不满足条件的行释放锁
# next-key lock是如何发挥作用的
# 场景一
表结构: column: id,c,d; id是主键, c是index
表中已有数据: (0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25)
| session A | session B | session C | |
|---|---|---|---|
| T1 | begin; select * from t where d=5 for update; | ||
| T2 | begin; insert into t values(8,8,8); (block) | ||
| T3 | begin; insert into t values(11,11,11); (block) |
场景一说明
- T1, 排他锁锁定全表数据(因为d没有索引).
- T2/T3, 插入数据都被阻塞, 因为全表都加入了next-key lock., 所有insert和update(更新原有数据)都无法执行, 但update不存在数据是可以的.
# 场景二, 锁发生在普通索引上
| session A | session B | session C | |
|---|---|---|---|
| T1 | begin; select * from t where c=5 for update; | ||
| T2 | begin; insert into t values(8,8,8); (block) | ||
| T3 | begin; update t set d=12 where c=10; (update成功) | ||
| T4 | update t set c=9 where id=10; (block) |
场景二说明
- T1, 根据索引锁定c(0,10), 所有c列值在此区间的都被阻塞. 根据c=5, 先锁定最小单位(0,5]的next-key lock, 由于是等值查询, 会向右遍历到不满足的索引, 并降级为间隙锁, 因此最终为(0,10).
- T3, 不在间隙锁索引区间中, 成功执行.
- T4, id虽然不在锁定区间内, 但想把id=10这条记录的c列更新到锁定区间范围内, 其实相当于在锁定区间insert数据, 因此会阻塞.
# 场景三(1), 但凡事有特例, 看下面
表结构: column: id,c,d; id是主键, c是唯一索引.
表数据: (0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25)
| session A | session B | session C | |
|---|---|---|---|
| T1 | begin; select * from t where id=5 for update; | ||
| T2 | begin; insert into t values(4,4,4); (insert成功) | ||
| T3 | begin; insert into t values(6,6,6); (insert成功) |
场景三说明
- T1, 根据索引锁定, 由于索引带有
唯一性, 故改为record lock, 只锁定单行.- T2/T3, 由于T1只锁定了单行, 故都能成功insert.
**查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。 **
注意: 通过主键或唯一索引来锁定不存在的值，也会产生gap锁。
insert只会产生排他锁(X), 没有next-key lock
update一条不存在的数据, 不会产生锁
# 场景三(2), 锁定一行不存在数据时
| session A | session B | session C | session D | |
|---|---|---|---|---|
| T1 | begin; select * from t where c=30 for update; | |||
| T2 | begin; insert into t values(26,26,26) (block) | |||
| T3 | begin; insert into t values(100,100,100); (block) | |||
| T4 | begin; insert into t values(24,24,24) (ok) |
场景三(2)说明
- T1, for update不存在的行, 产生了间隙锁, 锁范围是(25, +∞)
- T2/T3, 依照锁范围阻塞.
- T4, 不在锁范围, insert成功.
# 场景三(3), 锁定一行不存在数据时, 使用lock in share mode
结果与场景三(2)相同, for update与lock in share mode都会产生nexk-key lock
# 场景四, update相当于在锁定区间insert
以上场景中的session B/C改为使用update, 也会有一样的问题, 因为gap lock是锁定的索引, 如果update的结果相当于在索引中插入数据, 一样会被阻塞.
| session A | session B | session C | session D | |
|---|---|---|---|---|
| T1 | begin; select * from t where c=5 for update; | |||
| T2 | begin; update t set c=5 where id=0; (block) | |||
| T3 | begin; update t set c=10 where id=0; (block) | |||
| T4 | begin; update t set c=11 where id=0; (ok) |
场景四说明
- T1, 产生间隙锁, 等值查询会向右遍历, 因此锁范围是(0,10]
- T2/T3, 根据锁范围, 阻塞.
- T4, 执行成功.
# 场景五, 普通索引锁定区间需要与主键组合判断
根据
普通索引锁定区间时, 还是有一定区别的, 下面给出场景案例.
| session A | session B | session C | |
|---|---|---|---|
| T1 | begin; select * from t where c=5 for update; | ||
| T2 | begin; insert into t values(9, 10, 10); (block) | ||
| T3 | begin; insert into t values(11, 10, 10); (ok) |
以上场景, 间隙锁的范围是(id,c)->(0,0)~(10,10)之间的间隙都会被锁住, 而session C是(11,10)不在返回内, 因此可以insert成功.
此处是根据索引(btree)的有序性.
# 场景六(1), 只有被访问到的索引才会加锁
| session A | session B | session C | |
|---|---|---|---|
| T1 | begin; select id from t where c=5 lock in share mode; | ||
| T2 | begin; update t set d=100 where id=5; (ok) | ||
| T3 | begin; insert t values(6,6,6); (block) |
场景六说明
- T1, 给c=5这一行加上读锁, 并产生next-key lock, 锁定范围是c(0, 10);
- T2, 更新id=5的行, 由于T1使用的是覆盖索引, 只锁定c=5这个索引, 并没有锁定主键索引, 因此更新主键索引对应的数据行, 是可以的
- T3, insert的c列在锁定范围内, 故阻塞.
# 场景六(2), TODO
| session A | session B | session C | |
|---|---|---|---|
| T1 | begin; select id from t where c=5 for update; | ||
| T2 | begin; update t set d=100 where id=5; (block) | ||
| T3 | begin; insert t values(6,6,6); (block) |
# 场景七(1), 主键索引的范围锁定
| session A | session B | session C | |
|---|---|---|---|
| T1 | begin; select * from t where id>=10 and id<16 for update; | ||
| T2 | begin; insert into t values(11, 11, 11); (block) | ||
| T3 | begin; insert into t values(21, 21, 21); (ok) insert into t values(19, 19, 19); (block) |
场景七说明
- T1, 先由id=10的主键等值查询, 降级为行锁, 锁定id=10单行, 但由于范围查询, 故向右遍历, 直到不满足碰到不满足条件的索引(条件为id<16)停下来, 锁定范围是id[10,20]
- T2/T3, 根据锁定范围可知.
# 场景七(2), 主键范围锁定的扩展场景
| session A | session B | session C | |
|---|---|---|---|
| T1 | begin; select * from t where id>14 and id<16 for update; | ||
| T2 | begin; update t set d=100 where id=10; (ok) insert into t values(11, 11, 11); (block) | ||
| T3 | begin; update t set d=100 where id=20; (block) insert into t values(19, 19, 19); (block) |
场景七(2)说明
- T1, 首先根据id>14进行范围查询, 直到碰到不满足条件的索引(条件为id<16)停下来, 锁定范围是id(10,20]
- T2/T3, 根据锁定范围可知结果.
与场景七(1)差别在于T1时刻没有等值查询
# 场景七(3), 唯一属性索引的范围锁
| session A | session B | session C | session D | |
|---|---|---|---|---|
| T1 | begin; select * from t where id>10 and id<=15 for update; | |||
| T2 | begin; update t set d=100 where id=10 (block) | |||
| T3 | begin; insert into t values(19, 19, 19); (block) | |||
| T4 | begin; update t set d=100 where id=20 (block) |
场景七(3)说明
- T1, 锁定范围是id(10,20], 范围查询到id=15, 但是唯一性的索引的范围查询上需要向右查询, 就算最后的索引正好满足条件
# 场景八, limit语句加锁
假设表数据在原有基础上, 增加一行(30,10,30)
| session A | session B | |
|---|---|---|
| T1 | begin; delete from t where c=10 limit 2; | |
| T2 | begin; insert into t values(12, 12, 12); (ok) |
场景八说明
- T1, 因为limit 2导致索引遍历只做到第二个c=10, 不会继续向右遍历, 此时间隙锁范围减小, 锁范围是(c=10,id=10~c=10,id=30]
如果T1不加limit, 则锁范围是(c=10,id=10~c=15,id=15)
# 场景九, 死锁了?
| session A | session B | |
|---|---|---|
| T1 | begin; select id from t where c=10 lock in share mode; | |
| T2 | begin; update t set d=100 where c=10; (block) | |
| T3 | insert into t values(8, 8, 8); | |
| T4 | Deadlock found ... |
场景九说明
- T1, 语句加了next-key lock, 锁范围是(5,15].
- T2, update语句也要加上next-key lock, 锁定范围是(5,10], 但因为行锁冲突, 进入锁等待.
- T3, 由于session A的insert会被session B的间隙锁阻塞, 因此mysql发现死锁.
虽然T2时刻是在锁等待, 但是update语句的next-key lock是分间隙锁和行锁分开执行的, 先加间隙锁, 并且加锁成功, 加行锁才进入了锁等待, 最后导致死锁.
# next-key lock是动态变动的
场景:
session A中先for update锁定一个区间, 然后session B更新在区间中的一行数据到区间外并提交事务, 之后session C再将其他行数据更新到区间边缘(session B操作之前本不在锁定区间中), 此时操作被阻塞.
# 它会导致什么问题
next-key lock帮助我们一定程度上解决了幻读问题, 它除了牺牲性能, 还有什么问题?
# 容易出现死锁
在本专题的另一篇文章mysql的事务隔离级别介绍中说明了RR下出现死锁的问题, 其实就是由间隙锁导致的一个常见问题, 在问题下方也附上了解决方案.
# 总结重点
# 能分析出各种场景下next-key lock的情况
# nexk-key lock的简单实现原理
# 一些典型的或者容易忽略的注意事项
- update也会加next-key lock